import pandas as pd
from sqlalchemy import create_engine


def get_mac_rate(Moid):
    engine = create_engine(
        "mssql+pyodbc://pereader:pereader@172.18.65.31:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
        fast_executemany=True)

    # 得到分光站别的良率数据包括日期/工单号/数量/不良名称
    sql1 = 'SELECT ITEM_CODE,[质量标准] AS Qstandard FROM QA_Finished_Part_Spec'  # SE
    sql2 = 'SELECT DOC_NO as MoID,ITEM_CODE,ITEM_NAME from MO_ITEM '  # SF
    sql3 = 'SELECT Qstandard,MachineID,BinID,BinName FROM Sorting_BIN'  # bin类
    sql4 = 'SELECT MoID,LotID,MachineID,convert(char,UploadTime,23) as Date FROM Sorting_Info'  # SN 日期和工单号,日期进行处理
    sql6 = 'SELECT distinct top 5 MoID from Sorting_Data'
    # sql6=  list(sql6.MoID)
    # print( type(pd.read_sql(sql1, engine)))
    sql5 = f"SELECT MoID,LotID,BinID from Sorting_Data where MoID = '{Moid}'"  # SD

    # pandas读取
    df1 = pd.read_sql(sql1, engine)
    df2 = pd.read_sql(sql2, engine)
    df3 = pd.read_sql(sql3, engine)
    df4 = pd.read_sql(sql4, engine)
    df5 = pd.read_sql(sql5, engine)

    result1 = pd.merge(df1, df2, on='ITEM_CODE')  # SE\SF ITEM_CODE相同
    # print(result1)
    result2 = pd.merge(df2, df4, on='MoID')  # SN\SF MOID相同
    result2.drop(['Date','ITEM_NAME'], axis=1, inplace=True)
    # print(result2)

    data_set1 = pd.merge(result1, result2, on=['MoID', 'ITEM_CODE'])  # 合并B表
    # print(data_set1)
    # print(df5)
    result3 = pd.merge(df5, data_set1, on=['MoID', 'LotID'])
    # print(result3)
    # print(df3)
    data_set2 = pd.merge(df3, result3, on=['Qstandard', 'MachineID', 'BinID'])
    # print(data_set2)

    # 删除多余列
    data_set2.drop(['ITEM_CODE', 'LotID', 'Qstandard', 'MachineID', 'BinID'], axis=1, inplace=True)

    # 获取对应日期的不同Bin个数
    df_num = data_set2.groupby(['MoID', 'BinName']).size().reset_index()
    # df_num.to_excel("./kind.xlsx",index=False)

    # 获取订单-总数
    data_set2.drop(['BinName'], axis=1, inplace=True)
    df_total = data_set2.groupby(['MoID']).size().reset_index()
    # df_total.to_excel("./sum.xlsx",index=False)

    # 数据处理
    df_total.rename(columns={0: 'sum'}, inplace=True)  # 改列名
    df_num.rename(columns={0: 'num'}, inplace=True)  # 改列名
    # print(df_total)
    #print(df_num)

    list1 = [
        'MAC1', 'MAC1_rate', 'MAC2', 'MAC2_rate', 'MAC3', 'MAC3_rate', 'NG',
        'NG_rate'
    ]
    for i in list1:
        df_total[i] = 0
    #print(df_total)
    for columns in df_num.itertuples():
        # print(columns)
        # index,  MoID , BinName, Date, num
        # 以日期和单号找位置插入数据
        ID = getattr(columns, 'MoID')
        # insert_date = getattr(columns, 'Date')
        # 找到对应行,若超过两行,或未找到
        data_list = df_total[(df_total['MoID'] == ID)].index.tolist()
        if len(data_list) == 1:
            index = data_list[0]
        else:
            print('该行数据出错,{}'.format(getattr(columns, 'Index')))
            continue
        # MAC1
        if getattr(columns, 'BinName').find('MAC1') >= 0:
            # print(df_total.iloc[index])
            df_total.iloc[index, 2] = getattr(columns, 'num')
            df_total.iloc[index, 3] = format((getattr(columns, 'num') / df_total.iloc[index, 1] * 100),'0.2f')
        # MAC2
        elif getattr(columns, 'BinName').find('MAC2') >= 0:
            df_total.iloc[index, 4] = getattr(columns, 'num')
            df_total.iloc[index, 5] = format((getattr(columns, 'num') / df_total.iloc[index, 1] * 100),'0.2f')
        # MAC3
        elif getattr(columns, 'BinName').find('MAC3') >= 0:
            df_total.iloc[index, 6] = getattr(columns, 'num')
            df_total.iloc[index, 7] = format((getattr(columns, 'num') / df_total.iloc[index, 1] * 100), '0.2f')
        # 不良
        else:
            # 如果其他不良已经计算过,数量相加,比率重新计算
            if df_total.iloc[index, 8] == 0:
                num = getattr(columns, 'num')
            else:
                num = getattr(columns, 'num') + df_total.iloc[index, 8]
            df_total.iloc[index, 8] = num
            df_total.iloc[index, 9] = format(num / df_total.iloc[index, 1] * 100, '0.2f')

    for i in range(0, len(df_total)):
        if df_total.iloc[i, 2] == '':
            df_total.iloc[i, 2] = 0
            df_total.iloc[i, 3] = 0
        if df_total.iloc[i, 4] == '':
            # print(1)
            df_total.iloc[i, 4] = 0
            df_total.iloc[i, 5] = df_total.iloc[i, 3]
        else:
            num = df_total.iloc[i, 2] + df_total.iloc[i, 4]
            df_total.iloc[i, 5] = format(num / df_total.iloc[i, 1] * 100, '.2f')
        if df_total.iloc[i, 6] == '':
            # print(1)
            df_total.iloc[i, 6] = 0
            df_total.iloc[i, 7] = df_total.iloc[i, 6]
        else:
            num = df_total.iloc[i, 2] + df_total.iloc[i, 4] + df_total.iloc[i, 6]
            df_total.iloc[i, 7] = format(num / df_total.iloc[i, 1] * 100, '.2f')
    # 添加ITEM_NAME列
    df_total.loc[0,'ITEM_NAME'] = df2.loc[df2['MoID'] == Moid]['ITEM_NAME'].to_list()[0]
    # print(df2.loc[df2['MoID'] == Moid]['ITEM_NAME'].to_list()[0])
    df_total.drop(['sum'],axis=1,inplace=True)
    df_total.drop(['MoID'], axis=1, inplace=True)
    # print(type(df_total.iloc[2,7]))
    df_total.to_csv('Data_analyse.csv')
    return df_total

# get_mac_rate('5101-20030075')
